﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using VswService_V7.Utility;
using VswService_V7.DataBase.Tables.L2INTERFACE;

namespace VswService_V7.DataBase.Tables
{
    public partial class PIPE_INFO
    {
        #region field
        public static readonly string tableName = "PIPE_INFO";
        public static readonly string fd_eventNo = "EVENT_NO";
        public static readonly string fd_taskNo = "TASK_NO";
        public static readonly string fd_batchNo = "BATCH_NO";
        public static readonly string fd_stripNo = "STRIP_NO";
        public static readonly string fd_pipeNo = "PIPE_NO";
        public static readonly string fd_stripSeq = "STRIP_SEQ";
        public static readonly string fd_location = "LOCATION";
        public static readonly string fd_dtj = "DTJ";
        public static readonly string fd_length = "LENGTH";
        public static readonly string fd_qualityCode = "QUALITY_CODE";
        public static readonly string fd_sampleReason = "SMP_RS";
        public static readonly string fd_sampleFltLength = "SMP_F_LEN";
        public static readonly string fd_mechanicalLength = "SMP_M_LEN";
        public static readonly string fd_additionalLength = "SMP_A_LEN";
        public static readonly string fd_scrapLength = "SCRAP_LENGTH";
        public static readonly string fd_parameter = "PARAMETER";

        public static readonly string fd_headCut = "HEAD_CUT";
        public static readonly string fd_tailCut = "TAIL_CUT";

        public static readonly string fd_updateTime = "UPDATE_TIME";

        #endregion

        /// <summary>
        /// insert a pipe record into database,and return eventno
        /// </summary>
        /// <param name="pipe"></param>
        /// <returns></returns>
        public static long InsertNewPipeInfoRecord(PipeInformation pipe)
        {
            try
            {
                string pipeNum = pipe.GetFullPipeNo();
                string taskNo = STRIP_INFO.Select_TaskNoByStripNo(pipe.stripNo);

                DataTable taskTable = TASK_INFO.SelectTaskInfo(taskNo);

                //DataTable taskTable = STRIP_INFO.Select_TaskByStripNo(pipe.stripNo);
                string qualityCode = pipe.GetFullQcCode();
                long eventNo = OracleHelper.GetNextEventNo();

                if(taskTable != null&&taskTable.Rows.Count>0)
                {
                    string sql = string.Format("insert into {0}" +
                "({1},{2},{3},{4},{5}," +
                "{6},{7},{8},{9},{10}," +
                "{11},{12},{13},{14},{15}," +
                "{16}," +
                "{17}," +
                "{18},{19})" +
                "values({20},'{21}','{22}','{23}','{24}'," +
                "'{25}','{26}','{27}','{28}','{29}'," +
                "'{30}','{31}','{32}','{33}','{34}'," +
                "'{35}'," +
                "'{36}'," +
                "'{37}','{38}')",
                    tableName,
                    fd_eventNo, fd_taskNo, fd_batchNo, fd_stripNo, fd_pipeNo,
                    fd_stripSeq, fd_location, fd_dtj, fd_length, fd_qualityCode,
                    fd_sampleReason, fd_sampleFltLength, fd_mechanicalLength, fd_additionalLength, fd_scrapLength,
                    fd_parameter,
                    fd_updateTime,
                    fd_headCut,fd_tailCut,
                    eventNo, taskTable.Rows[0][TASK_INFO.fd_taskNo], pipe.batchNo, pipe.stripNo, pipeNum,
                    pipe.stripSeq, pipe.location, pipe.dtj, pipe.length, qualityCode,
                    pipe.sampleReason, pipe.flt_length, pipe.machinal_length, pipe.addition_length, pipe.scrap_length,
                    pipe.parameter,
                    pipe.updateTime,
                    pipe.headCut,pipe.tailCut);

                    string sqlINTF_PIPE_INFO = string.Format("insert into {0}" +
                   "({1},{2},{3},{4},{5}," +
                   "{6},{7},{8},{9},{10}," +
                   "{11},{12},{13},{14},{15}," +
                   "{16},{17},{18}," +
                   "{19})" +
                   "values({20},'{21}','{22}'," +
                   "'{23}','{24}','{25}','{26}','{27}'," +
                   "'{28}','{29}','{30}','{31}','{32}'," +
                   "'{33}','{34}','{35}','{36}','{37}'," +
                   "'{38}')",
                       L2INTF_PIPE_INFO.tableName,
                       L2INTF_PIPE_INFO.fd_eventNo, L2INTF_PIPE_INFO.fd_taskNo, L2INTF_PIPE_INFO.fd_batchNo, L2INTF_PIPE_INFO.fd_stripNo, L2INTF_PIPE_INFO.fd_pipeNo,
                       L2INTF_PIPE_INFO.fd_stripSeq, L2INTF_PIPE_INFO.fd_location, L2INTF_PIPE_INFO.fd_dtj, L2INTF_PIPE_INFO.fd_length, L2INTF_PIPE_INFO.fd_qualityCode,
                       L2INTF_PIPE_INFO.fd_sampleReason, L2INTF_PIPE_INFO.fd_sampleFltLength, L2INTF_PIPE_INFO.fd_mechanicalLength, L2INTF_PIPE_INFO.fd_additionalLength, L2INTF_PIPE_INFO.fd_scrapLength,
                       L2INTF_PIPE_INFO.fd_parameter,L2INTF_PIPE_INFO.fd_wallThk,L2INTF_PIPE_INFO.fd_Diameter,
                       L2INTF_PIPE_INFO.fd_updateTime,
                       eventNo, taskTable.Rows[0][TASK_INFO.fd_taskNo], pipe.batchNo, pipe.stripNo, pipeNum,
                       pipe.stripSeq, pipe.location, pipe.dtj, pipe.length, qualityCode,
                       pipe.sampleReason, pipe.flt_length, pipe.machinal_length, pipe.addition_length, pipe.scrap_length,
                       pipe.parameter, taskTable.Rows[0][TASK_INFO.fd_pipeNominalThickness], taskTable.Rows[0][TASK_INFO.fd_pipeNominalOd],
                       pipe.updateTime);
                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdatePipepassport, pipe.updateTime, "1", "L1", "");
                    List<string> list = new List<string>();
                    list.Add(sql);
                    list.Add(sqlINTF_PIPE_INFO);
                    list.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(list);
                    return eventNo;
                }
                else
                {
                    LogHelper.WriteLog("InsertNewPipeInfoRecord: bad pipe information");
                    return -1;
                }

                
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("InsertNewPipeInfoRecord", ex);
                return -1;
            }

        }


        /// <summary>
        /// insert some pipes' record into database from IS,and return sucess count
        /// </summary>
        /// <param name="pipes"></param>
        /// <returns></returns>
        public static int InsertNewPipeInfoRecords(List<PipeInformation> pipes)
        {
            try
            {
                List<string> sqlList = new List<string>();
                for(int i=0;i<pipes.Count;i++)
                {
                    string pipeNum = pipes[i].GetFullPipeNo();
                    string taskNo = STRIP_INFO.Select_TaskNoByStripNo(pipes[i].stripNo);

                    DataTable taskTable = TASK_INFO.SelectTaskInfo(taskNo);
                    string qualityCode = pipes[i].GetFullQcCode();
                    long eventNo = OracleHelper.GetNextEventNo();

                    if (taskTable != null && taskTable.Rows.Count > 0)
                    {
                        string sql = string.Format("insert into {0}" +
                    "({1},{2},{3},{4},{5}," +
                    "{6},{7},{8},{9},{10}," +
                    "{11},{12},{13},{14},{15}," +
                    "{16}," +
                    "{17}," +
                    "{18},{19})" +
                    "values({20},'{21}','{22}','{23}','{24}'," +
                    "'{25}','{26}','{27}','{28}','{29}'," +
                    "'{30}','{31}','{32}','{33}','{34}'," +
                    "'{35}'," +
                    "'{36}'," +
                    "'{37}','{38}')",
                        tableName,
                        fd_eventNo, fd_taskNo, fd_batchNo, fd_stripNo, fd_pipeNo,
                        fd_stripSeq, fd_location, fd_dtj, fd_length, fd_qualityCode,
                        fd_sampleReason, fd_sampleFltLength, fd_mechanicalLength, fd_additionalLength, fd_scrapLength,
                        fd_parameter,
                        fd_updateTime,
                        fd_headCut, fd_tailCut,
                        eventNo, taskTable.Rows[0][TASK_INFO.fd_taskNo], pipes[i].batchNo, pipes[i].stripNo, pipeNum,
                        pipes[i].stripSeq, pipes[i].location, pipes[i].dtj, pipes[i].length, qualityCode,
                        pipes[i].sampleReason, pipes[i].flt_length, pipes[i].machinal_length, pipes[i].addition_length, pipes[i].scrap_length,
                        pipes[i].parameter,
                        pipes[i].updateTime,
                        pipes[i].headCut, pipes[i].tailCut);

                        string sqlINTF_PIPE_INFO = string.Format("insert into {0}" +
                       "({1},{2},{3},{4},{5}," +
                       "{6},{7},{8},{9},{10}," +
                       "{11},{12},{13},{14},{15}," +
                       "{16},{17},{18}," +
                       "{19})" +
                       "values({20},'{21}','{22}'," +
                       "'{23}','{24}','{25}','{26}','{27}'," +
                       "'{28}','{29}','{30}','{31}','{32}'," +
                       "'{33}','{34}','{35}','{36}','{37}'," +
                       "'{38}')",
                           L2INTF_PIPE_INFO.tableName,
                           L2INTF_PIPE_INFO.fd_eventNo, L2INTF_PIPE_INFO.fd_taskNo, L2INTF_PIPE_INFO.fd_batchNo, L2INTF_PIPE_INFO.fd_stripNo, L2INTF_PIPE_INFO.fd_pipeNo,
                           L2INTF_PIPE_INFO.fd_stripSeq, L2INTF_PIPE_INFO.fd_location, L2INTF_PIPE_INFO.fd_dtj, L2INTF_PIPE_INFO.fd_length, L2INTF_PIPE_INFO.fd_qualityCode,
                           L2INTF_PIPE_INFO.fd_sampleReason, L2INTF_PIPE_INFO.fd_sampleFltLength, L2INTF_PIPE_INFO.fd_mechanicalLength, L2INTF_PIPE_INFO.fd_additionalLength, L2INTF_PIPE_INFO.fd_scrapLength,
                           L2INTF_PIPE_INFO.fd_parameter, L2INTF_PIPE_INFO.fd_wallThk, L2INTF_PIPE_INFO.fd_Diameter,
                           L2INTF_PIPE_INFO.fd_updateTime,
                           eventNo, taskTable.Rows[0][TASK_INFO.fd_taskNo], pipes[i].batchNo, pipes[i].stripNo, pipeNum,
                           pipes[i].stripSeq, pipes[i].location, pipes[i].dtj, pipes[i].length, qualityCode,
                           pipes[i].sampleReason, pipes[i].flt_length, pipes[i].machinal_length, pipes[i].addition_length, pipes[i].scrap_length,
                           pipes[i].parameter, taskTable.Rows[0][TASK_INFO.fd_pipeNominalThickness], taskTable.Rows[0][TASK_INFO.fd_pipeNominalOd],
                           pipes[i].updateTime);
                        string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                            L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdatePipepassport, pipes[i].updateTime, "1", "L1", "");
                        sqlList.Add(sql);
                        sqlList.Add(sqlINTF_PIPE_INFO);
                        sqlList.Add(sqlINTFEVENT);            
                    }
                    else
                    {
                        string sql = string.Format("insert into {0}" +
                   "({1},{2},{3},{4},{5}," +
                   "{6},{7},{8},{9},{10}," +
                   "{11},{12},{13},{14},{15}," +
                   "{16}," +
                   "{17}," +
                   "{18},{19})" +
                   "values({20},'{21}','{22}','{23}','{24}'," +
                   "'{25}','{26}','{27}','{28}','{29}'," +
                   "'{30}','{31}','{32}','{33}','{34}'," +
                   "'{35}'," +
                   "'{36}'," +
                   "'{37}','{38}')",
                       tableName,
                       fd_eventNo, fd_taskNo, fd_batchNo, fd_stripNo, fd_pipeNo,
                       fd_stripSeq, fd_location, fd_dtj, fd_length, fd_qualityCode,
                       fd_sampleReason, fd_sampleFltLength, fd_mechanicalLength, fd_additionalLength, fd_scrapLength,
                       fd_parameter,
                       fd_updateTime,
                       fd_headCut, fd_tailCut,
                       eventNo, 0, pipes[i].batchNo, pipes[i].stripNo, pipeNum,
                       pipes[i].stripSeq, pipes[i].location, pipes[i].dtj, pipes[i].length, qualityCode,
                       pipes[i].sampleReason, pipes[i].flt_length, pipes[i].machinal_length, pipes[i].addition_length, pipes[i].scrap_length,
                       pipes[i].parameter,
                       pipes[i].updateTime,
                       pipes[i].headCut, pipes[i].tailCut);

                        string sqlINTF_PIPE_INFO = string.Format("insert into {0}" +
                       "({1},{2},{3},{4},{5}," +
                       "{6},{7},{8},{9},{10}," +
                       "{11},{12},{13},{14},{15}," +
                       "{16},{17},{18}," +
                       "{19})" +
                       "values({20},'{21}','{22}'," +
                       "'{23}','{24}','{25}','{26}','{27}'," +
                       "'{28}','{29}','{30}','{31}','{32}'," +
                       "'{33}','{34}','{35}','{36}','{37}'," +
                       "'{38}')",
                           L2INTF_PIPE_INFO.tableName,
                           L2INTF_PIPE_INFO.fd_eventNo, L2INTF_PIPE_INFO.fd_taskNo, L2INTF_PIPE_INFO.fd_batchNo, L2INTF_PIPE_INFO.fd_stripNo, L2INTF_PIPE_INFO.fd_pipeNo,
                           L2INTF_PIPE_INFO.fd_stripSeq, L2INTF_PIPE_INFO.fd_location, L2INTF_PIPE_INFO.fd_dtj, L2INTF_PIPE_INFO.fd_length, L2INTF_PIPE_INFO.fd_qualityCode,
                           L2INTF_PIPE_INFO.fd_sampleReason, L2INTF_PIPE_INFO.fd_sampleFltLength, L2INTF_PIPE_INFO.fd_mechanicalLength, L2INTF_PIPE_INFO.fd_additionalLength, L2INTF_PIPE_INFO.fd_scrapLength,
                           L2INTF_PIPE_INFO.fd_parameter, L2INTF_PIPE_INFO.fd_wallThk, L2INTF_PIPE_INFO.fd_Diameter,
                           L2INTF_PIPE_INFO.fd_updateTime,
                           eventNo, 0, pipes[i].batchNo, pipes[i].stripNo, pipeNum,
                           pipes[i].stripSeq, pipes[i].location, pipes[i].dtj, pipes[i].length, qualityCode,
                           pipes[i].sampleReason, pipes[i].flt_length, pipes[i].machinal_length, pipes[i].addition_length, pipes[i].scrap_length,
                           pipes[i].parameter, 0, 0,
                           pipes[i].updateTime);
                        string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                            L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdatePipepassport, pipes[i].updateTime, "1", "L1", "");
                        sqlList.Add(sql);
                        sqlList.Add(sqlINTF_PIPE_INFO);
                        sqlList.Add(sqlINTFEVENT);
                    }
                }

                if(sqlList.Count>0 && sqlList.Count == pipes.Count*3)
                {
                    OracleHelper.ExecuteSqlTran(sqlList);
                    return sqlList.Count;
                }
                else
                {
                    return 0;
                }
                

            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("InsertNewPipeInfoRecords", ex);
                return 0;
            }

        }


        /// <summary>
        /// update location of pipe record,and return eventno
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <param name="time"></param>
        /// <param name="location"></param>
        /// <returns>eventNo</returns>
        public static long UpdatePipeInfoRecord(string pipeFullNo, DateTime time, string location)
        {
            try
            {
                DataTable dataTable = SelectLastTimePipe(pipeFullNo);
                string updateTime = time.ToString(Formats.GetDateFormat());
                string pipeLocation = location;
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    PipeInformation pipe = new PipeInformation(pipeLocation);
                    pipe.taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                    pipe.batchNo = dataTable.Rows[0][fd_batchNo].ToString();
                    pipe.stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                    pipe.pipeNo = dataTable.Rows[0][fd_pipeNo].ToString();
                    pipe.stripSeq = dataTable.Rows[0][fd_stripSeq].ToString();
                    pipe.location = location;
                    pipe.dtj = dataTable.Rows[0][fd_dtj].ToString();
                    pipe.length = dataTable.Rows[0][fd_length].ToString();
                    pipe.qualityCode = dataTable.Rows[0][fd_qualityCode].ToString();
                    pipe.sampleReason = dataTable.Rows[0][fd_sampleReason].ToString();
                    pipe.flt_length = dataTable.Rows[0][fd_sampleFltLength].ToString();
                    pipe.machinal_length = dataTable.Rows[0][fd_mechanicalLength].ToString();
                    pipe.addition_length = dataTable.Rows[0][fd_additionalLength].ToString();
                    pipe.scrap_length = dataTable.Rows[0][fd_scrapLength].ToString();
                    pipe.parameter = dataTable.Rows[0][fd_parameter].ToString();
                    pipe.updateTime = updateTime;
                    pipe.headCut= dataTable.Rows[0][fd_headCut].ToString();
                    pipe.tailCut = dataTable.Rows[0][fd_tailCut].ToString();
                    return InsertNewPipeInfoRecord(pipe);
                }
                else
                {
                    PipeInformation p = new PipeInformation("0", pipeFullNo, "65535", "65535", location);
                    return InsertNewIllegalPipeInfoRecord(p);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdatePipeInfoRecord", ex);
                throw ex;
            }
        }

        /// <summary>
        /// update pipe quality code.location of pipe record，and return eventNo
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <param name="time"></param>
        /// <param name="qCode"></param>
        /// <param name="location"></param>
        /// <returns>eventNo</returns>
        public static long UpdatePipeInfoMustRecord(string pipeFullNo, DateTime time, string qCode_low,string qCode_high, string location)
        {
            try
            {
                DataTable dataTable = SelectLastTimePipe(pipeFullNo);
                string updateTime = time.ToString(Formats.GetDateFormat());
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    PipeInformation pipe = new PipeInformation(location);
                    pipe.taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                    pipe.batchNo = dataTable.Rows[0][fd_batchNo].ToString();
                    pipe.stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                    pipe.pipeNo = dataTable.Rows[0][fd_pipeNo].ToString();
                    pipe.stripSeq = dataTable.Rows[0][fd_stripSeq].ToString();
                    //pipe.location = location;
                    pipe.dtj = dataTable.Rows[0][fd_dtj].ToString();
                    pipe.length = dataTable.Rows[0][fd_length].ToString();
                    pipe.qualityCode = qCode_low;
                    pipe.qualityCode2 = qCode_high;
                    pipe.sampleReason = dataTable.Rows[0][fd_sampleReason].ToString();
                    pipe.flt_length = dataTable.Rows[0][fd_sampleFltLength].ToString();
                    pipe.machinal_length = dataTable.Rows[0][fd_mechanicalLength].ToString();
                    pipe.addition_length = dataTable.Rows[0][fd_additionalLength].ToString();
                    pipe.scrap_length = dataTable.Rows[0][fd_scrapLength].ToString();
                    pipe.parameter = dataTable.Rows[0][fd_parameter].ToString();
                    pipe.updateTime = updateTime;
                    return InsertNewPipeInfoRecord(pipe);
                }
                else
                {
                    PipeInformation p = new PipeInformation("0", pipeFullNo, "65535", "65535", location);
                    return InsertNewIllegalPipeInfoRecord(p);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdatePipeInfoRecord", ex);
                throw ex;
            }
        }

        /// <summary>
        /// update sample reason, pipe length,location of pipe,and generate sample record，return eventNo
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <param name="time"></param>
        /// <param name="sampleLength"></param>
        /// <param name="location"></param>
        /// <returns>eventNo</returns>
        public static long UpdatePipeInfoRecord(string pipeFullNo, DateTime time, string sampleReason, string sampleLength, string location, ref SampleInfo sample)
        {
            try
            {
                DataTable dataTable = SelectLastTimePipe(pipeFullNo);
                string updateTime = time.ToString(Formats.GetDateFormat());
                string pipeLocation = location;
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    //只会在头上切采样
                    string dtj = (int.Parse(dataTable.Rows[0][fd_dtj].ToString()) + int.Parse(sampleLength)).ToString();
                    string length = (int.Parse(dataTable.Rows[0][fd_length].ToString()) - int.Parse(sampleLength)).ToString();
                    PipeInformation pipe = new PipeInformation(location);
                    pipe.taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                    pipe.batchNo = dataTable.Rows[0][fd_batchNo].ToString();
                    pipe.stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                    pipe.pipeNo = dataTable.Rows[0][fd_pipeNo].ToString();
                    pipe.stripSeq = dataTable.Rows[0][fd_stripSeq].ToString();
                    //pipe.location = pipeLocation;
                    pipe.dtj = dtj;
                    pipe.length = length;
                    pipe.qualityCode = dataTable.Rows[0][fd_qualityCode].ToString();
                    pipe.sampleReason = sampleReason;
                    pipe.flt_length = dataTable.Rows[0][fd_sampleFltLength].ToString();
                    pipe.machinal_length = dataTable.Rows[0][fd_mechanicalLength].ToString();
                    pipe.addition_length = dataTable.Rows[0][fd_additionalLength].ToString();
                    pipe.scrap_length = dataTable.Rows[0][fd_scrapLength].ToString();
                    pipe.parameter = dataTable.Rows[0][fd_parameter].ToString();
                    pipe.updateTime = updateTime;

                    sample.taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                    sample.stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                    sample.batchNo = dataTable.Rows[0][fd_batchNo].ToString();
                    sample.pipeNo = dataTable.Rows[0][fd_pipeNo].ToString();
                    sample.stripSeq = dataTable.Rows[0][fd_stripSeq].ToString();
                    sample.dtj = dataTable.Rows[0][fd_dtj].ToString();
                    sample.updateTime = updateTime;
                    return InsertNewPipeInfoRecord(pipe);
                }
                else
                {
                    PipeInformation p = new PipeInformation("0", pipeFullNo, "65535", "65535", location);
                    return InsertNewIllegalPipeInfoRecord(p);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdatePipeInfoRecord", ex);
                throw ex;
            }
        }

        /// <summary>
        /// update pipe length, qc ,location of the pipe record，and return eventNo
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <param name="time"></param>
        /// <param name="headCut">head cutting amount</param>
        /// <param name="rearCut">tail cutting amount</param>
        /// <param name="location"></param>
        /// <returns></returns>
        public static long UpdatePipeInfoPlsRecord(string pipeNo, DateTime time, string headCut, string rearCut,string qc_code_low,string qc_code_high,string pipe_length, string location)
        {
            try
            {
                DataTable dataTable = SelectLastTimePipe(pipeNo);
                string updateTime = time.ToString(Formats.GetDateFormat());
                string pipeLocation = location;
                if (dataTable != null && dataTable.Rows.Count > 0)
                {
                    //dtj=dtj_old + headCut
                    string dtj = (int.Parse(dataTable.Rows[0][fd_dtj].ToString()) + int.Parse(headCut)).ToString();
                    string length = (int.Parse(dataTable.Rows[0][fd_length].ToString()) - int.Parse(headCut) - int.Parse(rearCut)).ToString();
                    PipeInformation pipe = new PipeInformation(location);
                    pipe.taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                    pipe.batchNo = dataTable.Rows[0][fd_batchNo].ToString();
                    pipe.stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                    pipe.pipeNo = dataTable.Rows[0][fd_pipeNo].ToString();
                    pipe.stripSeq = dataTable.Rows[0][fd_stripSeq].ToString();
                    //pipe.location = pipeLocation;
                    pipe.dtj = dtj;
                    pipe.length = length;

                    pipe.qualityCode = qc_code_low;
                    pipe.qualityCode2 = qc_code_high;
                    pipe.sampleReason = dataTable.Rows[0][fd_sampleReason].ToString();
                    pipe.flt_length = dataTable.Rows[0][fd_sampleFltLength].ToString();
                    pipe.machinal_length = dataTable.Rows[0][fd_mechanicalLength].ToString();
                    pipe.addition_length = dataTable.Rows[0][fd_additionalLength].ToString();
                    pipe.scrap_length = dataTable.Rows[0][fd_scrapLength].ToString();
                    pipe.parameter = dataTable.Rows[0][fd_parameter].ToString();
                    pipe.updateTime = updateTime;
                    pipe.tailCut = rearCut;
                    pipe.headCut = headCut;
                    return InsertPipeInfoRecordOnPls(pipe, dataTable.Rows[0][fd_qualityCode].ToString());
                }
                else
                {
                    PipeInformation p = new PipeInformation("0", pipeNo, "65535", "65535", location);
                    return InsertNewPipeInfoRecord(p);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdatePipeInfoRecord", ex);
                throw ex;
            }
        }


        public static long UpdatePipeInfoOnVis(string pipeNo, string qc_code_low, string qc_code_high,string location)
        {
            try
            {
                DataTable dataTable = SelectLastTimePipe(pipeNo);
                string updateTime = DateTime.Now.ToString(Formats.GetDateFormat());
                string pipeLocation = location;
                if (dataTable != null && dataTable.Rows.Count > 0)
                {

                    string qstring = dataTable.Rows[0][fd_qualityCode].ToString();
                    string qhigh = qstring.Substring(0,qstring.Length - 5);

                    //dtj=dtj_old + headCut
                    PipeInformation pipe = new PipeInformation(location);
                    pipe.taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                    pipe.batchNo = dataTable.Rows[0][fd_batchNo].ToString();
                    pipe.stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                    pipe.pipeNo = dataTable.Rows[0][fd_pipeNo].ToString();
                    pipe.stripSeq = dataTable.Rows[0][fd_stripSeq].ToString();
                    //pipe.location = pipeLocation;
                    pipe.dtj = dataTable.Rows[0][fd_dtj].ToString();
                    pipe.length = dataTable.Rows[0][fd_length].ToString();

                    pipe.qualityCode = qc_code_low;
                    pipe.qualityCode2 = qhigh;


                    pipe.sampleReason = dataTable.Rows[0][fd_sampleReason].ToString();
                    pipe.flt_length = dataTable.Rows[0][fd_sampleFltLength].ToString();
                    pipe.machinal_length = dataTable.Rows[0][fd_mechanicalLength].ToString();
                    pipe.addition_length = dataTable.Rows[0][fd_additionalLength].ToString();
                    pipe.scrap_length = dataTable.Rows[0][fd_scrapLength].ToString();
                    pipe.parameter = dataTable.Rows[0][fd_parameter].ToString();
                    pipe.updateTime = updateTime;
                    pipe.tailCut = dataTable.Rows[0][fd_headCut].ToString();
                    pipe.headCut = dataTable.Rows[0][fd_headCut].ToString();

                    string qualityCode = pipe.GetFullQcCode();
                    return InsertPipeInfoRecordOnPls(pipe, qualityCode);
                }
                else
                {
                    PipeInformation p = new PipeInformation("0", pipeNo, "65535", "65535", location);
                    return InsertNewPipeInfoRecord(p);
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdatePipeInfoRecord", ex);
                throw ex;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="pipe"></param>
        /// <param name="qcCode"></param>
        /// <returns></returns>
        public static long InsertPipeInfoRecordOnPls(PipeInformation pipe,string qcCode)
        {
            try
            {
                string pipeNum = pipe.GetFullPipeNo();
                string taskNo = STRIP_INFO.Select_TaskNoByStripNo(pipe.stripNo);

                DataTable taskTable = TASK_INFO.SelectTaskInfo(taskNo);

                //DataTable taskTable = STRIP_INFO.Select_TaskByStripNo(pipe.stripNo);
                string qualityCode = qcCode;
                long eventNo = OracleHelper.GetNextEventNo();

                if (taskTable != null && taskTable.Rows.Count > 0)
                {
                    string sql = string.Format("insert into {0}" +
                "({1},{2},{3},{4},{5}," +
                "{6},{7},{8},{9},{10}," +
                "{11},{12},{13},{14},{15}," +
                "{16}," +
                "{17}," +
                "{18},{19})" +
                "values({20},'{21}','{22}','{23}','{24}'," +
                "'{25}','{26}','{27}','{28}','{29}'," +
                "'{30}','{31}','{32}','{33}','{34}'," +
                "'{35}'," +
                "'{36}'," +
                "'{37}','{38}')",
                    tableName,
                    fd_eventNo, fd_taskNo, fd_batchNo, fd_stripNo, fd_pipeNo,
                    fd_stripSeq, fd_location, fd_dtj, fd_length, fd_qualityCode,
                    fd_sampleReason, fd_sampleFltLength, fd_mechanicalLength, fd_additionalLength, fd_scrapLength,
                    fd_parameter,
                    fd_updateTime,
                    fd_headCut, fd_tailCut,
                    eventNo, taskTable.Rows[0][TASK_INFO.fd_taskNo], pipe.batchNo, pipe.stripNo, pipeNum,
                    pipe.stripSeq, pipe.location, pipe.dtj, pipe.length, qualityCode,
                    pipe.sampleReason, pipe.flt_length, pipe.machinal_length, pipe.addition_length, pipe.scrap_length,
                    pipe.parameter,
                    pipe.updateTime,
                    pipe.headCut, pipe.tailCut);

                    string sqlINTF_PIPE_INFO = string.Format("insert into {0}" +
                   "({1},{2},{3},{4},{5}," +
                   "{6},{7},{8},{9},{10}," +
                   "{11},{12},{13},{14},{15}," +
                   "{16},{17},{18}," +
                   "{19})" +
                   "values({20},'{21}','{22}'," +
                   "'{23}','{24}','{25}','{26}','{27}'," +
                   "'{28}','{29}','{30}','{31}','{32}'," +
                   "'{33}','{34}','{35}','{36}','{37}'," +
                   "'{38}')",
                       L2INTF_PIPE_INFO.tableName,
                       L2INTF_PIPE_INFO.fd_eventNo, L2INTF_PIPE_INFO.fd_taskNo, L2INTF_PIPE_INFO.fd_batchNo, L2INTF_PIPE_INFO.fd_stripNo, L2INTF_PIPE_INFO.fd_pipeNo,
                       L2INTF_PIPE_INFO.fd_stripSeq, L2INTF_PIPE_INFO.fd_location, L2INTF_PIPE_INFO.fd_dtj, L2INTF_PIPE_INFO.fd_length, L2INTF_PIPE_INFO.fd_qualityCode,
                       L2INTF_PIPE_INFO.fd_sampleReason, L2INTF_PIPE_INFO.fd_sampleFltLength, L2INTF_PIPE_INFO.fd_mechanicalLength, L2INTF_PIPE_INFO.fd_additionalLength, L2INTF_PIPE_INFO.fd_scrapLength,
                       L2INTF_PIPE_INFO.fd_parameter, L2INTF_PIPE_INFO.fd_wallThk, L2INTF_PIPE_INFO.fd_Diameter,
                       L2INTF_PIPE_INFO.fd_updateTime,
                       eventNo, taskTable.Rows[0][TASK_INFO.fd_taskNo], pipe.batchNo, pipe.stripNo, pipeNum,
                       pipe.stripSeq, pipe.location, pipe.dtj, pipe.length, qualityCode,
                       pipe.sampleReason, pipe.flt_length, pipe.machinal_length, pipe.addition_length, pipe.scrap_length,
                       pipe.parameter, taskTable.Rows[0][TASK_INFO.fd_pipeNominalThickness], taskTable.Rows[0][TASK_INFO.fd_pipeNominalOd],
                       pipe.updateTime);
                    string sqlINTFEVENT = string.Format("insert into {0} values({1},'{2}','{3}','{4}','{5}','{6}')",
                        L2INTF_EVENT.tableName, eventNo, L2INTF_EVENT.generateOrUpdatePipepassport, pipe.updateTime, "1", "L1", "");
                    List<string> list = new List<string>();
                    list.Add(sql);
                    list.Add(sqlINTF_PIPE_INFO);
                    list.Add(sqlINTFEVENT);
                    OracleHelper.ExecuteSqlTran(list);
                    return eventNo;
                }
                else
                {
                    LogHelper.WriteLog("InsertNewPipeInfoRecord: bad pipe information");
                    return -1;
                }


            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("InsertNewPipeInfoRecord", ex);
                return -1;
            }

        }

        /// <summary>
        /// 更新在 is 扫入的 pipe 的结果
        /// </summary>
        /// <param name="batchNo"></param>
        /// <param name="pipeNo"></param>
        /// <param name="location"></param>
        /// <param name="pipeLength"></param>
        /// <returns></returns>
        public static int UpdatePipeInfoOnIs(List<string> batchNo, List<string> pipeNo, string location, List<string> pipeLength)
        {
            try
            {
                if(batchNo.Count==pipeNo.Count&&pipeNo.Count==pipeLength.Count)
                {
                    List<PipeInformation> pipes = new List<PipeInformation>();
                    for(int i=0;i<batchNo.Count;i++)
                    {
                        int pipeNum = int.Parse(pipeNo[i]);
                        string fullPipeNumber = batchNo[i] + pipeNum.ToString("000");
                        DataTable dataTable = SelectLastTimePipe(fullPipeNumber);
                        string updateTime = DateTime.Now.ToString(Formats.GetDateFormat());
                        if (dataTable != null && dataTable.Rows.Count > 0)
                        {
                            PipeInformation pipe = new PipeInformation(location);
                            pipe.taskNo = dataTable.Rows[0][fd_taskNo].ToString();
                            pipe.batchNo = dataTable.Rows[0][fd_batchNo].ToString();
                            pipe.stripNo = dataTable.Rows[0][fd_stripNo].ToString();
                            pipe.pipeNo = dataTable.Rows[0][fd_pipeNo].ToString();
                            pipe.stripSeq = dataTable.Rows[0][fd_stripSeq].ToString();
                            //pipe.location = location;
                            pipe.dtj = dataTable.Rows[0][fd_dtj].ToString();
                            pipe.length = pipeLength[i];

                            string qc = dataTable.Rows[0][fd_qualityCode].ToString();
                            if(qc.Length>5)
                            {
                                pipe.qualityCode = qc.Substring(qc.Length - 5);
                                pipe.qualityCode2 = qc.Substring(0, qc.Length - 5);
                            }
                            else
                            {
                                pipe.qualityCode = qc;
                                pipe.qualityCode2 = "0";
                            }

                            pipe.sampleReason = dataTable.Rows[0][fd_sampleReason].ToString();
                            pipe.flt_length = dataTable.Rows[0][fd_sampleFltLength].ToString();
                            pipe.machinal_length = dataTable.Rows[0][fd_mechanicalLength].ToString();
                            pipe.addition_length = dataTable.Rows[0][fd_additionalLength].ToString();
                            pipe.scrap_length = dataTable.Rows[0][fd_scrapLength].ToString();
                            pipe.parameter = dataTable.Rows[0][fd_parameter].ToString();
                            pipe.updateTime = updateTime;
                            pipes.Add(pipe);
                        }
                        else
                        {
                            PipeInformation p = new PipeInformation(batchNo[i], pipeNo[i], "0", "0", location);
                            p.length = pipeLength[i];
                            pipes.Add(p);
                        }

                    }
                    if(pipes.Count==batchNo.Count)
                    {
                        return InsertNewPipeInfoRecords(pipes);
                    }
                    else
                    {
                        return 0;
                    }
                }
                else
                {
                    LogHelper.WriteLog("UpdatePipeInfoOnIs", new Exception("batchNo , pipeNO and pipeLength count is not match. "));
                    return 0;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("UpdatePipeInfoRecord", ex);
                throw ex;
            }
        }


        /// <summary>
        /// When the received PipeNo cannot be queried, the current pipe is considered to be an illegal pipe
        /// </summary>
        /// <param name="pipe"></param>
        /// <returns></returns>
        public static long InsertNewIllegalPipeInfoRecord(PipeInformation pipe)
        {
            long eventNo = OracleHelper.GetNextEventNo();
            string sql = string.Format("insert into {0}" +
            "({1},{2},{3},{4},{5}," +
            "{6},{7},{8},{9},{10}," +
            "{11},{12},{13},{14},{15}," +
            "{16}," +
            "{17}," +
            "{18},{19})" +
            "values({20},'{21}','{22}','{23}','{24}'," +
            "'{25}','{26}','{27}','{28}','{29}'," +
            "'{30}','{31}','{32}','{33}','{34}'," +
            "'{35}'," +
            "'{36}'," +
            "'{37}','{38}')",
                tableName,
                fd_eventNo, fd_taskNo, fd_batchNo, fd_stripNo, fd_pipeNo,
                fd_stripSeq, fd_location, fd_dtj, fd_length, fd_qualityCode,
                fd_sampleReason, fd_sampleFltLength, fd_mechanicalLength, fd_additionalLength, fd_scrapLength,
                fd_parameter,
                fd_updateTime,
                fd_headCut, fd_tailCut,
                eventNo, pipe.taskNo, pipe.batchNo, pipe.stripNo, pipe.GetFullPipeNo(),
                pipe.stripSeq, pipe.location, pipe.dtj, pipe.length, pipe.GetFullQcCode(),
                pipe.sampleReason, pipe.flt_length, pipe.machinal_length, pipe.addition_length, pipe.scrap_length,
                pipe.parameter,
                pipe.updateTime,
                pipe.headCut, pipe.tailCut);

            try
            {
                int count = OracleHelper.Exct_NonQuery(sql);
                if (count > 0)
                    return eventNo;
                else
                    return -1;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("InsertNewIllegalPipeInfoRecord", ex);
                throw ex;
            }
        }

        /// <summary>
        /// Find the latest record of pipeNo
        /// </summary>
        /// <param name="pipeNo"></param>
        /// <returns></returns>
		public static DataTable SelectLastTimePipe(string pipeNo)
        {
            try
            {
                //string sql = string.Format("select * from (select t.*,row_number()over(order by {0} desc)rn from {1} t where {2}='{3}')t1 where rn=1", fd_updateTime, tableName, fd_pipeNo, pipeNo);
                string sql = string.Format("select * from (select * from {0} where {1}='{2}' order by {3} desc) where rownum=1", tableName, fd_pipeNo, pipeNo, fd_updateTime);
                DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                if (dataTable != null && dataTable.Rows.Count == 1)
                {
                    return dataTable;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectLastTimePipe", ex);
                throw ex;
            }
        }


        public static List<string> SelectLastTimePipeLength(List<string> pipeNos)
        {
            try
            {
                List<string> pipeLength = new List<string>();
                if (pipeNos.Count>0)
                {
                    
                    for(int i=0;i< pipeNos.Count;i++)
                    {
                        //string sql = string.Format("select * from (select t.*,row_number()over(order by {0} desc)rn from {1} t where {2}='{3}')t1 where rn=1", fd_updateTime, tableName, fd_pipeNo, pipeNos[i]);
                        string sql = string.Format("select * from (select * from {0} where {1}='{2}' order by {3} desc) where rownum=1", tableName, fd_pipeNo, pipeNos[i], fd_updateTime);
                        DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
                        if (dataTable != null && dataTable.Rows.Count == 1)
                        {
                            pipeLength.Add(dataTable.Rows[0][fd_length].ToString());
                        }
                        else
                        {
                            pipeLength.Add("0");
                        }
                    }
                }

                return pipeLength;


            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("SelectLastTimePipeLength", ex);
                throw ex;
            }
        }
    }

    /// <summary>
    /// used for pipe_info and sample_info
    /// </summary>
    public class PipeInformation
    {
        public PipeInformation()
        {
            eventNo = 0;
            taskNo = "0";
            batchNo = "0";
            stripNo = "0";
            pipeNo = "0";
            stripSeq = "0";
            location = "0";
            dtj = "0";
            length = "0";
            qualityCode = "0";
            qualityCode2 = "0";
            sampleReason = "0";
            flt_length = "0";
            machinal_length = "0";
            addition_length = "0";
            scrap_length = "0";
            parameter = "0";

            updateTime = "0";
            headCut = "0";
            tailCut = "0";
        }

        public PipeInformation(string pipeLocation)
        {
            eventNo = 0;
            taskNo = "0";
            batchNo = "0";
            stripNo = "0";
            pipeNo = "0";
            stripSeq = "0";
            location = pipeLocation;
            dtj = "0";
            length = "0";
            qualityCode = "0";
            qualityCode2 = "0";
            sampleReason = "0";
            flt_length = "0";
            machinal_length = "0";
            addition_length = "0";
            scrap_length = "0";
            parameter = "0";
            updateTime = "0";
            headCut = "0";
            tailCut = "0";
        }


        public PipeInformation(string batchNumber, string pipeNumber, string qc1,string qc2, string pipeLocation)
        {
            eventNo = 0;
            taskNo = "0";
            batchNo = batchNumber;
            stripNo = "0";
            pipeNo = pipeNumber;
            stripSeq = "0";
            location = pipeLocation;
            dtj = "0";
            length = "0";
            qualityCode = qc1;
            qualityCode2 = qc2;
            sampleReason = "0";
            flt_length = "0";
            machinal_length = "0";
            addition_length = "0";
            scrap_length = "0";
            parameter = "0";
            updateTime = "0";
            headCut = "0";
            tailCut = "0";
        }

        public long eventNo;
        public string taskNo;
        public string batchNo;
        public string stripNo;
        public string pipeNo;
        public string stripSeq;
        public string location;
        public string dtj;
        public string length;
        public string qualityCode;
        public string qualityCode2;
        public string sampleReason;
        public string flt_length;
        public string machinal_length;
        public string addition_length;
        public string scrap_length;
        public string parameter;
        public string updateTime;
        public string headCut;
        public string tailCut;

        public string GetFullPipeNo()
        {
            if (pipeNo.Length > 3)
                return pipeNo;
            else
            {
                Int64 pipeNum = Int64.Parse(pipeNo);
                string fullPipeNumber = batchNo + pipeNum.ToString("000");
                return fullPipeNumber;
            }
        }

        public string GetShortPipeNo()
        {
            if (pipeNo.Length <= 3)
                return pipeNo;
            else
            {
                string shortPipeNumber = pipeNo.Substring(pipeNo.Length-3,3);
                return shortPipeNumber;
            }
        }

        public string GetFullQcCode()
        {
            int q1 = int.Parse(qualityCode);
            return qualityCode2 + q1.ToString("00000");
        }

    }
}
